package com.ExcelServer.service.impl;


import com.ExcelServer.common.Result;
import com.ExcelServer.mapper.ExcelCreateInfoMapper;
import com.ExcelServer.mapper.ExcelRulesMapper;
import com.ExcelServer.pojo.entity.ExcelCreateInfo;
import com.ExcelServer.pojo.entity.ExcelRules;
import com.ExcelServer.pojo.form.add.AddOneExcelRulerForm;
import com.ExcelServer.pojo.form.delete.DeleteExcelForm;
import com.ExcelServer.pojo.form.update.UpdateExcelForm;
import com.ExcelServer.pojo.query.ExcelInfoQuery;
import com.ExcelServer.pojo.query.TableInfoQuery;
import com.ExcelServer.service.ExcelCreateInfoService;
import com.ExcelServer.util.CompareJsonObjectsUtil;
import com.ExcelServer.util.EquationVerification;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;

import com.googlecode.aviator.AviatorEvaluator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;


/**
 * @author admin
 * @description 针对表【excel_create_info(保存excel -> mysql表的记录 )】的数据库操作Service实现
 * @createDate 2023-12-27 10:57:48
 */
@Service
public class ExcelCreateInfoServiceImpl extends ServiceImpl<ExcelCreateInfoMapper, ExcelCreateInfo>
        implements ExcelCreateInfoService {

    @Autowired
    ExcelCreateInfoMapper excelCreateInfoMapper;

    @Autowired
    CompareJsonObjectsUtil compareJsonObjectsUtil;

    @Autowired
    ExcelRulesMapper excelRulesMapper;


    @Autowired
    EquationVerification equationVerification;

    @Override
    public int input(ExcelInfoQuery excelInfoQuery) {
        int count = 0;
        List list = excelInfoQuery.getList();
        Map map = new HashMap<>();

        if (list.size() > 0) {
            if (list.get(0) instanceof Map) {
                map = (Map) list.get(0);

                // 然后继续处理 map 对象
                System.out.println("map ");
            } else {
                System.out.println(list.get(0));
                System.out.println("not map");
                // 如果第一个元素不是 Map 类型，可以进行相应的错误处理或者日志记录
            }
            //查看记录表中是否存在表头记录信息。

            List<ExcelCreateInfo> list1 = this.list();
            ExcelCreateInfo temp = new ExcelCreateInfo();
            boolean IsLive = false;
            for (int i = 0; i < list1.size(); i++) {
                System.out.println("------------------------------------------");

                Object property = list1.get(i).getProperty();

                String json1 = JSON.toJSONString(map);
                json1 = json1.replaceAll(" ", "");

                String json2 = property.toString();
                json2 = json2.replaceAll(" ", "");

                System.out.println(json1);
                System.out.println(json2);
                // 调用自定义的json判断器 判断两个JSON对象是否相等 以便于进入到下一个入口
                boolean flag = false;
                try {
                    flag  = compareJsonObjectsUtil.compareJsonObjects(json1, json2);
                }catch (Exception e){
                    e.printStackTrace();
                }

                if (flag) {
                    System.out.println("两个对象对应的JSON对象相等");
                    IsLive = true;
                    temp = list1.get(i);
                    break;
                } else {
                    System.out.println("两个对象对应的JSON对象不相等");
                }
            }
            if (IsLive) {
                //直接往已存在的表中添加数据
                //插入数据并且去重。
                System.out.println("存在该表");
                System.out.println("存入开始");

                System.out.println("tableName:"+temp.getTableName());
                for (int i = 1; i < list.size(); i++) {
                    count++;
                    Map<String, String> map1 = (Map<String, String>) list.get(i);
                    String keys = map1.keySet().stream()
                            .map(key -> "`" + key + "`")
                            .collect(Collectors.joining(", "));

                    String values = map1.entrySet().stream()
                            .map(entry -> entry.getValue() != null ? "'" + entry.getValue() + "'" : "null")
                            .collect(Collectors.joining(", "));

                    String line = "(" + keys + ") VALUES (" + values + ")";
                    System.out.println(temp.getTableName());
                    excelCreateInfoMapper.insertToOtherTable(temp.getTableName(), line);
                }
                System.out.println("存入完成");

            } else {

                //列信息
                String columnNames = String.join(" VARCHAR(255), ", map.keySet()) + " VARCHAR(255)";
                //表名

                Integer last = excelCreateInfoMapper.getLastId();
                String tableName;
                if(last!=null){
                     tableName = "excel" + (last + 1);
                }else {
                     tableName = "excel" +  1;
                }


                //创建新表
                excelCreateInfoMapper.createTable(columnNames, tableName);

                //将新表信息记录下来。到控制总表中。
                ExcelCreateInfo excelCreateInfo = new ExcelCreateInfo();
                excelCreateInfo.setTableName(tableName);
                String s = JSON.toJSONString(map);

                excelCreateInfo.setProperty(s);
                excelCreateInfo.setExcelName(excelInfoQuery.getName());

                excelCreateInfoMapper.insert(excelCreateInfo);

                for (int i = 1; i < list.size(); i++) {
                    count++;
                    Map<String, String> map1 = (Map<String, String>) list.get(i);
                    String keys = map1.keySet().stream()
                            .map(key -> "`" + key + "`")
                            .collect(Collectors.joining(", "));

                    String values = map1.entrySet().stream()
                            .map(entry -> entry.getValue() != null ? "'" + entry.getValue() + "'" : "NULL")
                            .collect(Collectors.joining(", "));

                    String line = "(" + keys + ") VALUES (" + values + ")";

                    //无中生有是++
                    excelCreateInfoMapper.insertToOtherTable(tableName, line);
                }

            }
            return count;
        }

        return 0;
    }

    @Override
    public Result getExcelListInfo() {
        List<ExcelCreateInfo> list = list();
        return Result.success(list);
    }




    /**
     * 查询原型：select * from tableName where <script> 构造 like 语句进行查询 （若是没有 1=1 ）<script/>
     *
     * */
    @Override
    public Result getExcelInfoById(TableInfoQuery query) {

        ExcelCreateInfo excelCreateInfo = excelCreateInfoMapper.selectById(query.getTableId());
        Object property =  excelCreateInfo.getProperty();

        Map<String,Object> map = new LinkedHashMap<>();
        map.put("tableHeader",property);

        String tableName = excelCreateInfo.getTableName();
        List<Map<String,Object>> list;

        //构造查询条件
        String condition="";
        Map<String, String> queryQuery = query.getQuery();
        for (Map.Entry<String, String> entry : queryQuery.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();

            if (value != null && !value.isEmpty()) {
                // 拼接条件
                condition += "`"+key+"`" + " LIKE '%" + value + "%' AND ";
            }

        }
        System.out.println("构造的查询条件为：" + condition);
        // 去掉最后多余的 "AND"
        if (!condition.isEmpty()) {
            condition = condition.substring(0, condition.length() - 5);
        }else {
            condition="1=1";
        }

        System.out.println("构造的查询条件为：" + condition);

        list =  excelCreateInfoMapper.getExcelInfoByTableName(tableName,condition);

        //提取key
        listMapAddProperty(property, list);
        System.out.println("list"+list);

        List result = ChangeListByRulers(tableName,list);


        if(list.size()==0){
            return Result.success("不存在该表");
        }

        map.put("tableBody",result);
        return Result.success(map);
    }


    //表名校验，防止非法表名输入获取到非开放式的表格内容。
    public static boolean validateExcelNumberString(String str) {
        if (str.startsWith("excel")) {
            String number = str.substring(5); // 截取从第5个字符开始的子字符串
            return number.matches("\\d+"); // 使用正则表达式匹配纯数字
        }
        return false;
    }


    //可能需要的功能：分页
    @Override
    public Result getExcelInfoByByTableName(String tableName) {
        //参数合法性校验，保障系统安全。
        boolean validFormat = validateExcelNumberString(tableName);
        if(!validFormat){
            return Result.fail("系统错误，该表名称不正确，请输入正确的表名");
        }
        QueryWrapper<ExcelCreateInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("table_name",tableName);
        ExcelCreateInfo excelCreateInfo = new ExcelCreateInfo();
        List<ExcelCreateInfo> excelCreateInfos = excelCreateInfoMapper.selectList(queryWrapper);
        if(excelCreateInfos.size()>1){
            return Result.fail("系统出错，不可能存在两个相同的表格名称");
        }else if(excelCreateInfos.size()==1){
            excelCreateInfo = excelCreateInfos.get(0);
        }
        Map<String,Object> map = new LinkedHashMap<>();
        Object property = excelCreateInfo.getProperty();
        map.put("tableHeader",property);

        String condition="1=1";

        List<Map<String,Object>> list= excelCreateInfoMapper.getExcelInfoByTableName(tableName, condition);
        //提取key
        listMapAddProperty(property, list);

        /******规则返回添加start **/
        List result = ChangeListByRulers(tableName,list);
        /******规则返回添加end ***/
        map.put("tableBody",result);

        return Result.success(result);
    }

    private void listMapAddProperty(Object property, List<Map<String, Object>> list) {
        List<String> keys = new ArrayList<>();
        try {
            ObjectMapper objectMapper = new ObjectMapper();
            JsonNode rootNode = objectMapper.readTree( String.valueOf(property));

            Iterator<String> fieldNames = rootNode.fieldNames();

            while (fieldNames.hasNext()) {
                String key = fieldNames.next();
                keys.add(key);
            }

            System.out.println(keys);
        }catch (Exception e){
            e.printStackTrace();
        }
        for (Map<String, Object> temp : list) {
            List<String> tempKeys = new ArrayList<>(temp.keySet());
            System.out.println(tempKeys);
            for (String key : keys) {
                if (!tempKeys.contains(key)) {
                    temp.put(key, "");
                }
            }

        }
    }

    @Override
    public Result updateExcelInfoById(UpdateExcelForm updateExcelForm) {

        // 根据excelChangeForm获取tableName和修改的信息
        Map<String, Object> map = updateExcelForm.getMap();
        Integer tableId = updateExcelForm.getTableId();
        Integer rowId = updateExcelForm.getRowId();

        // 获取tableName
        ExcelCreateInfo excelCreateInfo = this.getById(tableId);
        String tableName = excelCreateInfo.getTableName();

        // 拼接条件 id=rowId
        String condition = "id=" + rowId;

        // 拼接line
        StringBuilder lineBuilder = new StringBuilder();
        int i = 0;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            String temp = "`"+key + "`=" + "'"+value+"'";
            if (++i < map.size()) {
                temp += ",";
            }
            lineBuilder.append(temp);
        }
        String line = lineBuilder.toString();

        System.out.println(line);
        boolean b = excelCreateInfoMapper.updateExcelInfoById(tableName, line, condition);
        if (b) {
            return Result.success("修改完成");
        }
        return Result.fail("未知错误");
    }

    @Override
    public Result deleteExcelInfoById(DeleteExcelForm deleteExcelForm) {

        Integer tableId = deleteExcelForm.getTableId();
        Integer rowId = deleteExcelForm.getRowId();

        // 获取tableName
        ExcelCreateInfo excelCreateInfo = this.getById(tableId);
        String tableName = excelCreateInfo.getTableName();

        // 拼接条件 id=rowId
        String condition = "id=" + rowId;

        boolean b = excelCreateInfoMapper.deleteExcelInfoById(tableName, condition);
        if (b) {
            return Result.success("修改完成");
        }
        return Result.fail("未知错误");
    }

    @Override
    public Result deleteExcelById(String tableId) {
        ExcelCreateInfo byId = this.getById(tableId);
        String condition="1=1";
        List<Map<String, Object>> excelInfoByTableName = excelCreateInfoMapper.getExcelInfoByTableName(byId.getTableName(), condition);
        int size = excelInfoByTableName.size();
        if(size>0){
            return Result.fail("删除失败,不允许删除非空表格");
        }

        excelCreateInfoMapper.deleteExcelByTableName(byId.getTableName());

        int i = excelCreateInfoMapper.deleteById(tableId);
        System.out.println("i :"+i);
        if(i>0){
            return Result.success("删除成功");
        }
        return Result.fail("删除失败,请重试");
    }

    @Override
    public Result addExcelRuleById(AddOneExcelRulerForm addOneExcelRulerForm) {
        Integer tableId = addOneExcelRulerForm.getTableId();
        ExcelCreateInfo excelCreateInfo;
        excelCreateInfo = this.getById(tableId);
        if(excelCreateInfo!=null){
            //校验 addOneExcelRulerForm.getTableRule();
            String tableRule = addOneExcelRulerForm.getTableRule();
            //step1 校验表中是否存在等式的所有的操作表示数 ，缺一个都不合法，直接返回。
            Object property = excelCreateInfo.getProperty();
            List<String> keys = new ArrayList<>();
            try {
                ObjectMapper objectMapper = new ObjectMapper();
                JsonNode rootNode = objectMapper.readTree( String.valueOf(property));

                Iterator<String> fieldNames = rootNode.fieldNames();

                while (fieldNames.hasNext()) {
                    String key = fieldNames.next();
                    keys.add(key);
                }
                System.out.println(keys);
            }catch (Exception e){
                e.printStackTrace();
            }

            //正则表达式获取tableRule 中的操作数。
            List<String> operands = new ArrayList<>();
            Pattern pattern = Pattern.compile("_\\d+");
            Matcher matcher = pattern.matcher(tableRule);
            while (matcher.find()) {
                String operand = matcher.group();
                operands.add(operand);
            }

            //检验参数是否合法
            for (String operand : operands) {
                if (keys.contains(operand)) {
                    continue;
                } else {
                    return Result.fail( "公式不正确,存在非法参数");
                }
            }

            //step2 检验具体某一个表的某一个列是否已经存在公式，若是存在，不允许该公式再进去（公式是以列为最小单位）
            QueryWrapper<ExcelRules> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("table_id",tableId);
            List<ExcelRules> step2 = excelRulesMapper.selectList(queryWrapper);

            String[] parts_put = tableRule.split("=");
            String rule = parts_put[0];
            if(parts_put[0].length()>parts_put[1].length()){
                rule=parts_put[1];
            }

            for (ExcelRules rules : step2) {
                String tableRuleTemp = rules.getTableRule();
                String[] parts = tableRuleTemp.split("=");
                String result = parts[0];
                if (parts[0].length() > parts[1].length()) {
                    result = parts[1];
                }
                if (result.equals(rule)) {
                    return Result.fail("添加失败，该列已经存在计算公式");
                }
            }


            //step3 校验等式是否合法，不合法也返回
            boolean verification = equationVerification.verification(tableRule);

            if(verification){
                ExcelRules excelRules = new ExcelRules();
                excelRules.setTableRule(tableRule);
                excelRules.setNote(addOneExcelRulerForm.getNote());
                excelRules.setTableId(tableId);

                excelRulesMapper.insert(excelRules);
                return Result.success("公式添加成功");
            }else {
                System.out.println("verification"+verification);
                return Result.fail("规则不正确");
            }

        }
        return Result.success("一切ok");
    }

    @Override
    public Result deleteExcelByIdF(String tableId) {
        ExcelCreateInfo byId = this.getById(tableId);
        excelCreateInfoMapper.deleteExcelByTableName(byId.getTableName());
        int i = excelCreateInfoMapper.deleteById(tableId);
        System.out.println("i :"+i);
        if(i>0){
            return Result.success("删除成功");
        }
        return Result.fail("删除失败,请重试");
    }




    //TODO 根据规则修改信息做一个自动计算的功能
    private List ChangeListByRulers(String tableName,List list) {

        /*************处理规则计算 start****************/

        //获取table_id
        QueryWrapper<ExcelCreateInfo> excelCreateInfoQueryWrapper = new QueryWrapper<>();
        excelCreateInfoQueryWrapper.eq("table_name",tableName);
        ExcelCreateInfo one = this.getOne(excelCreateInfoQueryWrapper);
        Integer tableId = one.getId();

        //获取rules[]
        QueryWrapper<ExcelRules> excelRulesQueryWrapper = new QueryWrapper<>();
        excelRulesQueryWrapper.eq("table_id",tableId);
        List<ExcelRules> excelRules = excelRulesMapper.selectList(excelRulesQueryWrapper);
        String result="";
        if(excelRules.size()>0){

            //按照规则组处理规则。
            for (ExcelRules excelRule : excelRules) {
                for (Object o : list) {
                    String tableRuler = excelRule.getTableRule();

                    System.out.println("公式:"+tableRuler);
                    Map<String, Object> obj = (HashMap<String, Object>)o;

                    String[] parts_put = tableRuler.split("=");
                    String ruleKey = parts_put[0];
                    if(parts_put[0].length()>parts_put[1].length()){
                        ruleKey =parts_put[1];
                    }

                    for (Map.Entry<String, Object> entry : obj.entrySet()) {
                        String key = entry.getKey();
                        Object value = entry.getValue();
                        if(!value.equals("")||!key.contains("_")){
                            BigDecimal bigDecimal;
                            try {
                                // 在这里执行你希望执行的操作，比如将其转换为 BigDecimal
                                bigDecimal = new BigDecimal(value.toString());
                                System.out.println(bigDecimal);
                                if(!key.equals(ruleKey)){
                                    tableRuler = tableRuler.replaceAll(key, bigDecimal +"M");
                                }
                                else {
                                    result= ruleKey;
                                }
                                // 继续处理...
                            } catch (NumberFormatException e) {
                                // 对象无法转换为 double 类型的处理逻辑
                                System.out.println("对象无法转换为 BigDecimal 类型");
                            }
                            System.out.println("Key: " + key + ", Value: " + value);
                        }else {
                            //记录下计算后的结果是哪个键 key
                            if(key.contains("_")){
                                result=key;
                            }

                        }
                    }
                    System.out.println("代入:"+tableRuler);
                    //设置高精度
//                    AviatorEvaluator.setOption(Options.MATH_CONTEXT,true);
                    Object execute =  AviatorEvaluator.execute(tableRuler);
                    System.out.println("========运算结果：==========");
                    System.out.println(execute);
                    System.out.println(((HashMap<String, Object>) o).get(ruleKey).toString());
                    //让属性值与tableRule的内容结合起来，根据tableRule计算规则的等式结果赋值回list内。
                    //触发自动计算回调，将自动计算之后的结果赋值回表格中。 //TODO
                    if (!execute.toString().equals(((HashMap<String, Object>) o).get(ruleKey).toString())) {

                        ((HashMap<String, Object>) o).replace(ruleKey,execute.toString());
                        UpdateExcelForm updateExcelForm = new UpdateExcelForm();
                        updateExcelForm.setMap((Map<String, Object>) o);

                        updateExcelForm.setRowId(Integer.valueOf(((HashMap<String, Object>) o).get("id").toString()));
                        updateExcelForm.setTableId(tableId);
                        updateExcelInfoById(updateExcelForm);
                    }




                    System.out.println("==================");
                    System.out.println();
                    System.out.println();
                }
            }
        }

        /*************处理规则计算  end  ****************/

        return list;
    }





}
